using System;
using System.Data;
using System.Collections.Specialized;

using System.Data.SqlClient;
using Evaluant.Uss.Commands;
using Evaluant.Uss.Common;
using Evaluant.Uss.SqlMapper;
using System.Collections;

namespace Evaluant.Uss.Sql
{
    /// <summary>
    /// Visitor pattern to execute concrete commands
    /// </summary>
    public class SqlCommandProcessor : ICommandProcessor
    {
        #region Members

        private IDbTransaction _Transaction;
        private string _NewId;
        private StringDictionary _NewIds = new StringDictionary();
        private SqlPersistenceEngine _Engine;

        #endregion

        #region IDbCommands
        private IDbCommand insertEntity;
        private IDbCommand deleteEntityReference;
        private IDbCommand deleteEntityAttribute;
        private IDbCommand deleteEntity;
        private Hashtable createAttributes = new Hashtable();
        private Hashtable updateAttributes = new Hashtable();
        private IDbCommand deleteAttribute;
        private IDbCommand createReference;
        private IDbCommand deleteReference;

        #endregion

        #region Ctor

        public SqlCommandProcessor(SqlPersistenceEngine engine)
        {
            this._Engine = engine;
        }

        #endregion

        #region Properties

        public IDbTransaction Transaction
        {
            get { return _Transaction; }
            set { _Transaction = value; }
        }

        public string NewId
        {
            get { return _NewId; }
            set { _NewId = value; }
        }


        public StringDictionary NewIds
        {
            get { return _NewIds; }
            set { _NewIds = value; }
        }

        private IDbConnection Connection
        {
            get { return _Engine.Connection; }
        }

        private IDriver Driver
        {
            get { return _Engine.Driver; }
        }

        private DBDialect Dialect
        {
            get { return _Engine.Dialect; }
        }

        private SqlMappingDataType MappingType
        {
            get { return _Engine.MappingType; }
        }

        #endregion

        #region Process Commands

        public void Process(Command c)
        {
        }

        public void Process(CreateEntityCommand c)
        {
            if (insertEntity == null)
            {
                string query = String.Format(@"INSERT INTO {0} ({1},{2}) VALUES ({3},{4})",
                                     Dialect.FormatAttribute(_Engine.EntityTableName),
                                     Dialect.FormatAttribute("Id"),
                                     Dialect.FormatAttribute("Type"),
                                     Driver.FormatParameter("Id"),
                                     Driver.FormatParameter("Type"));

                insertEntity = Driver.CreateCommand(query, Connection);
            }

            insertEntity.Parameters.Clear();
            insertEntity.Parameters.Add(Driver.CreateParameter("Id", c.ParentId, DbType.AnsiStringFixedLength, 36));
            insertEntity.Parameters.Add(Driver.CreateParameter("Type", DbType.AnsiString, c.Type));
            insertEntity.Transaction = _Transaction;
            insertEntity.ExecuteNonQuery();
        }

        public void Process(CompoundCreateCommand c)
        {
            Process((CreateEntityCommand)c);
            foreach (CreateAttributeCommand cc in c.InnerCommands)
                cc.Accept(this);
        }

        public void Process(CompoundUpdateCommand c)
        {
            foreach (Command cc in c.InnerCommands)
                cc.Accept(this);
        }

        public void Process(DeleteEntityCommand c)
        {

            if (deleteEntityReference == null)
            {
                // Deletes all references to and from it first
                string query = String.Format(@"DELETE FROM {0} WHERE ({1} = {2} or {3} = {2})",
                                       Dialect.FormatAttribute(_Engine.ReferenceTableName),
                                       Dialect.FormatAttribute("FK_Child"),
                                       Driver.FormatParameter("Id"),
                                       Dialect.FormatAttribute("FK_Parent"));

                deleteEntityReference = Driver.CreateCommand(query, Connection);
            }

            deleteEntityReference.Parameters.Clear();
            deleteEntityReference.Parameters.Add(Driver.CreateParameter("Id", DbType.AnsiStringFixedLength, c.ParentId));
            deleteEntityReference.Transaction = _Transaction;
            deleteEntityReference.ExecuteNonQuery();

            if (deleteEntityAttribute == null)
            {
                // Deletes all attributes first
                string query = String.Format(@"DELETE FROM {0} WHERE ({1} = {2})",
                                       Dialect.FormatAttribute(_Engine.AttributeTableName),
                                       Dialect.FormatAttribute("FK_Entity"),
                                       Driver.FormatParameter("Id"));

                deleteEntityAttribute = Driver.CreateCommand(query, Connection);
            }

            deleteEntityAttribute.Parameters.Clear();
            deleteEntityAttribute.Parameters.Add(Driver.CreateParameter("Id", DbType.AnsiStringFixedLength, c.ParentId));
            deleteEntityAttribute.Transaction = _Transaction;
            deleteEntityAttribute.ExecuteNonQuery();

            if (deleteEntity == null)
            {
                // Delete entity
                string query = String.Format(@"DELETE FROM {0} WHERE ({1} = {2})",
                                       Dialect.FormatAttribute(_Engine.EntityTableName),
                                       Dialect.FormatAttribute("Id"),
                                       Driver.FormatParameter("Id"));

                deleteEntity = Driver.CreateCommand(query, Connection);
            }

            deleteEntity.Parameters.Clear();
            deleteEntity.Parameters.Add(Driver.CreateParameter("Id", DbType.AnsiStringFixedLength, c.ParentId));
            deleteEntity.Transaction = _Transaction;
            deleteEntity.ExecuteNonQuery();
        }

        public void Process(CreateAttributeCommand c)
        {
            Type mdType = _Engine.Model.GetAttribute(c.ParentType, c.Name, true).Type;
            DbType attrDbType = GetDbTypeFromType(mdType, MappingType);

            string column = GetAttributeColumnNameFromDbType(attrDbType);
            IDbCommand createAttribute = null;
            if(createAttributes.Contains(column))
                createAttribute = createAttributes[column] as IDbCommand;
            
            if (createAttribute == null)
            {
                string query = String.Format(@"INSERT INTO {0} ({1}, {2}, {3}) VALUES ({4}, {5}, {6})",
                                                Dialect.FormatAttribute(_Engine.AttributeTableName),
                                                Dialect.FormatAttribute("Name"),
                                                Dialect.FormatAttribute("FK_Entity"),
                                                Dialect.FormatAttribute(column),
                                                Driver.FormatParameter("Name"),
                                                Driver.FormatParameter("FK_Entity"),
                                                Driver.FormatParameter("Value"));

                createAttribute = Driver.CreateCommand(query, Connection);
            }

            object attrValue = _Engine.Dialect.PreProcessValue(GetAttributeValue(mdType, c.Value));

            createAttribute.Parameters.Clear();
            createAttribute.Parameters.Add(Driver.CreateParameter("Name", DbType.AnsiString, c.Name));
            createAttribute.Parameters.Add(Driver.CreateParameter("FK_Entity", DbType.AnsiStringFixedLength, c.ParentId));
            createAttribute.Parameters.Add(Driver.CreateParameter("Value", attrDbType, attrValue));
            createAttribute.Transaction = _Transaction;
            createAttribute.ExecuteNonQuery();
        }

        public void Process(UpdateAttributeCommand c)
        {
            // Treat null values as a DeleteAttributeCommand in this engine
            if (c.Value == null)
            {
                DeleteAttributeCommand dac = new DeleteAttributeCommand(c.ParentId, c.ParentType, c.Name, c.Type, c.Value);
                Process(dac);
                return;
            }

            Type mdType = _Engine.Model.GetAttribute(c.ParentType, c.Name, true).Type;
            DbType attrDbType = GetDbTypeFromType(mdType, MappingType);

            string column = GetAttributeColumnNameFromDbType(attrDbType);
            IDbCommand updateAttribute = null;
            if (updateAttributes.Contains(column))
                updateAttribute = updateAttributes[column] as IDbCommand;

            if (updateAttribute == null)
            {
                string query = String.Format(@"UPDATE {0} SET {1} = {2} WHERE ({3} = {4} and {5} = {6})",
                                Dialect.FormatAttribute(_Engine.AttributeTableName),
                                Dialect.FormatAttribute(GetAttributeColumnNameFromDbType(attrDbType)),
                                Driver.FormatParameter("Value"),
                                Dialect.FormatAttribute("FK_Entity"),
                                Driver.FormatParameter("Id"),
                                Dialect.FormatAttribute("Name"),
                                Driver.FormatParameter("Name"));

                updateAttribute = Driver.CreateCommand(query, Connection);
            }

            object attrValue = _Engine.Dialect.PreProcessValue(GetAttributeValue(mdType, c.Value));

            updateAttribute.Parameters.Clear();
            updateAttribute.Parameters.Add(Driver.CreateParameter("Value", attrDbType, attrValue));
            updateAttribute.Parameters.Add(Driver.CreateParameter("Id", DbType.AnsiStringFixedLength, c.ParentId));
            updateAttribute.Parameters.Add(Driver.CreateParameter("Name", DbType.AnsiString, c.Name));
            updateAttribute.Transaction = _Transaction;
            updateAttribute.ExecuteNonQuery();
        }

        public void Process(DeleteAttributeCommand c)
        {
            if (deleteAttribute == null)
            {
                string query = String.Format(@"DELETE FROM {0} WHERE ({1} = {2} and {3} = {4})",
                                   Dialect.FormatAttribute(_Engine.AttributeTableName),
                                   Dialect.FormatAttribute("FK_Entity"),
                                   Driver.FormatParameter("Id"),
                                   Dialect.FormatAttribute("Name"),
                                   Driver.FormatParameter("Name"));

                deleteAttribute = Driver.CreateCommand(query, Connection);
            }

            deleteAttribute.Parameters.Clear();
            deleteAttribute.Parameters.Add(Driver.CreateParameter("Id", DbType.AnsiStringFixedLength, c.ParentId));
            deleteAttribute.Parameters.Add(Driver.CreateParameter("Name", DbType.AnsiString, c.Name));
            deleteAttribute.Transaction = _Transaction;
            deleteAttribute.ExecuteNonQuery();
        }

        public void Process(CreateReferenceCommand c)
        {
            if (createReference == null)
            {
                string query = String.Format(@"INSERT INTO {0} ({1}, {2}, {3}) VALUES ({4}, {5}, {6})",
                           Dialect.FormatAttribute(_Engine.ReferenceTableName),
                           Dialect.FormatAttribute("Role"),
                           Dialect.FormatAttribute("FK_Parent"),
                           Dialect.FormatAttribute("FK_Child"),
                           Driver.FormatParameter("Role"),
                           Driver.FormatParameter("Id_Parent"),
                           Driver.FormatParameter("Id_Child"));

                createReference = Driver.CreateCommand(query, Connection);
            }

            createReference.Parameters.Clear();
            createReference.Parameters.Add(Driver.CreateParameter("Role", DbType.AnsiString, c.Role));
            createReference.Parameters.Add(Driver.CreateParameter("Id_Parent", DbType.AnsiStringFixedLength, c.ParentId));
            createReference.Parameters.Add(Driver.CreateParameter("Id_Child", DbType.AnsiStringFixedLength, c.ChildId));
            createReference.Transaction = _Transaction;
            createReference.ExecuteNonQuery();
        }

        public void Process(DeleteReferenceCommand c)
        {
            if (deleteReference == null)
            {
                string query = String.Format(@"DELETE FROM {0} WHERE ({1} = {2} and {3} = {4})",
                           Dialect.FormatAttribute(_Engine.ReferenceTableName),
                           Dialect.FormatAttribute("FK_Parent"),
                           Driver.FormatParameter("Id_Parent"),
                           Dialect.FormatAttribute("FK_Child"),
                           Driver.FormatParameter("Id_Child"));

                deleteReference = Driver.CreateCommand(query, Connection);
            }
            
            deleteReference.Parameters.Clear();
            deleteReference.Parameters.Add(Driver.CreateParameter("Id_Parent", DbType.AnsiStringFixedLength, c.ParentId));
            deleteReference.Parameters.Add(Driver.CreateParameter("Id_Child", DbType.AnsiStringFixedLength, c.ChildId));
            deleteReference.Transaction = _Transaction;
            deleteReference.ExecuteNonQuery();
        }

        #endregion

        #region Parameters

        static internal DbType GetDbTypeFromType(Type type, SqlMappingDataType mappingType)
        {
#if !EUSS11
            // Is Nullable type ?
            if (type.IsGenericType && type.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
                return GetDbTypeFromType(type.GetGenericArguments()[0], mappingType);

#endif

            string strDbType = String.Empty;

            switch (Type.GetTypeCode(type))
            {
                case TypeCode.Boolean:
                    strDbType = mappingType.Bool.Name;
                    break;

                case TypeCode.Char:
                case TypeCode.String:
                    strDbType = mappingType.String.Name;
                    break;

                case TypeCode.Byte:
                case TypeCode.SByte:
                case TypeCode.Int16:
                case TypeCode.Int32:
                case TypeCode.Int64:
                case TypeCode.UInt16:
                case TypeCode.UInt32:
                case TypeCode.UInt64:
                    strDbType = mappingType.Int.Name;
                    break;

                case TypeCode.DateTime:
                    strDbType = mappingType.DateTime.Name;
                    break;

                case TypeCode.Decimal:
                case TypeCode.Double:
                case TypeCode.Single:
                    strDbType = mappingType.Float.Name;
                    break;

                case TypeCode.Object:
                    strDbType = mappingType.Binary.Name;
                    break;

                default:
                    strDbType = mappingType.String.Name;
                    break;
            }
            return (DbType)Enum.Parse(typeof(DbType), strDbType, true);
        }


        static internal string GetAttributeColumnNameFromDbType(DbType type)
        {
            switch (type)
            {
                case DbType.Boolean:
                    return SqlPersistenceEngine.BooleanValueColumn;

                case DbType.Guid:
                case DbType.AnsiString:
                case DbType.AnsiStringFixedLength:
                case DbType.String:
                case DbType.StringFixedLength:
                    return SqlPersistenceEngine.StringValueColumn;

                case DbType.VarNumeric:
                case DbType.Currency:
                case DbType.Decimal:
                case DbType.Double:
                case DbType.Single:
                    return SqlPersistenceEngine.FloatValueColumn;

                case DbType.Object:
                case DbType.Binary:
                    return SqlPersistenceEngine.BinaryValueColumn;

                case DbType.Date:
                case DbType.DateTime:
                case DbType.Time:
                    return SqlPersistenceEngine.DateTimeValueColumn;

                case DbType.Byte:
                case DbType.Int16:
                case DbType.Int32:
                case DbType.Int64:
                case DbType.SByte:
                case DbType.UInt16:
                case DbType.UInt32:
                case DbType.UInt64:
                    return SqlPersistenceEngine.IntegerValueColumn;

                default:
                    return SqlPersistenceEngine.StringValueColumn;
            }
        }

        protected virtual object GetAttributeValue(Type type, object value)
        {
            if (type == typeof(byte[]))
                return value;
            else if (!(type.IsValueType || type == typeof(String))) // string is not a ValueType
                return Utils.SerializeToArray(value);

            return value;
        }

        #endregion
    }
}
